package com.apware.utils.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

import com.apware.utils.config.Config;
import com.apware.utils.entity.CarPartsInfo;

import jxl.Cell;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
/**
 * 景瑞
 * @author Administrator
 *
 */
public class JRCarPartsInfoReader {

	static Logger log = Logger.getLogger(JRCarPartsInfoReader.class);

	//jingrui excel format
	String excelFilePath = Config.exportedFile + ".xls";
	String excelFilePath2 = Config.exportedFile +"2" +".xls";
	public static final Integer PARTS_NO = 0;
	public static final Integer PARTS_NAME = 1;
	public static final Integer PARTS_CAR = 2;
	public static final Integer PARTS_MANUF_NAME = 3;
	public static final Integer PARTS_PRODUCING = 4;
	public static final Integer PARTS_UNIT = 5;
	public static final Integer PARTS_STOCK = 6;
	public static final Integer PARTS_PRICE  =7;
	public static final Integer PARTS_BATCH_PRICE =8;

	public static final Integer PARTS_WAREHOUSE = 9;




	//poi reader
	public List<CarPartsInfo> getCarPartsList() {
		List<CarPartsInfo> list = new ArrayList<CarPartsInfo>();
		Config.readConfig();
		excelFilePath = Config.exportedFile + ".xls";
		FileInputStream inputStream = null;
		try {
			File f = new File(excelFilePath);
			System.out.println(f.getCanonicalPath());
			if(!f.exists()) return null;
			inputStream = new FileInputStream(f);

		} catch (FileNotFoundException e1) {
			log.error(e1);
			return null;
		} catch (IOException e) {
			log.error(e);
		}

		org.apache.poi.ss.usermodel.Workbook workbook = null;
		try {
			workbook = new HSSFWorkbook(inputStream);
		} catch (IOException e) {
			log.error(e);
			return null;
		}
		org.apache.poi.ss.usermodel.Sheet firstSheet = workbook.getSheetAt(0);
		Iterator<Row> iterator = firstSheet.iterator();

		while (iterator.hasNext()) {
			Row nextRow = iterator.next();
			CarPartsInfo c = new CarPartsInfo();
			if(nextRow.getCell(0) != null && nextRow.getCell(0).getStringCellValue().equals("partno"))
				continue;
			if(nextRow.getCell(PARTS_NO).getStringCellValue() != null ) {
				c.setPartsNo(nextRow.getCell(PARTS_NO).getStringCellValue());
				if(nextRow.getCell(PARTS_NO) != null)
					c.setStandardPartsNo(nextRow.getCell(PARTS_NO).getStringCellValue());

				if(nextRow.getCell(PARTS_NAME) != null)
					c.setCname(nextRow.getCell(PARTS_NAME).getStringCellValue());
				c.setEname(null);
				c.setIsOem("0");
				if(nextRow.getCell(PARTS_MANUF_NAME) != null) {
					c.setManufactureCname(nextRow.getCell(PARTS_MANUF_NAME).getStringCellValue());
					c.setManufactureEname(null);
				}
				if(nextRow.getCell(PARTS_PRODUCING) != null) { 
					c.setProducingArea(nextRow.getCell(PARTS_PRODUCING).getStringCellValue());
				}

				if(nextRow.getCell(PARTS_CAR) != null) 
					c.setCarInfo(nextRow.getCell(PARTS_CAR).getStringCellValue());

				if(nextRow.getCell(PARTS_UNIT) != null)
					c.setUnit(nextRow.getCell(PARTS_UNIT).getStringCellValue());

				if(nextRow.getCell(PARTS_STOCK) != null)
					c.setStock((long) nextRow.getCell(PARTS_STOCK).getNumericCellValue());
				//if(nextRow.getCell(PARTS_BATCH_PRICE) != null) 
				//		c.setBatchPrice(nextRow.getCell(PARTS_BATCH_PRICE).getNumericCellValue());
				c.setBatchPrice(0);
				if(nextRow.getCell(PARTS_PRICE) != null) 
					c.setPrice(nextRow.getCell(PARTS_PRICE).getNumericCellValue()); 
				c.setCostPrice(0);
				c.setOwnNo(nextRow.getCell(PARTS_WAREHOUSE).getStringCellValue());
				//				System.out.println(c.toString());
				list.add(c);
			}
		}

		try {
			workbook.close();
			inputStream.close();
			archiveXls(excelFilePath);

		} catch (IOException e) {
			log.error(e);
		}
		return list;
	}

	/**
	 * 读取xls文件内容
	 * @param file 想要读取的文件对象
	 * @return 返回文件内容
	 */
	public List<CarPartsInfo> getCarPartsListByJxl(){
		List<CarPartsInfo> list = new ArrayList<CarPartsInfo>();
		Config.readConfig();
		excelFilePath = Config.exportedFile + ".xls";
		excelFilePath2 = Config.exportedFile+"2"+ ".xls";
		FileInputStream inputStream = null;
		FileInputStream inputStream2 = null;
		File f=null;
		File f2=null;
		
		try {
			//D:/hxd.xls
			f = new File(excelFilePath);
			f2 = new File(excelFilePath2);
			// f = new File("D:/hxd.xls");
			//f2 = new File("D:/hxd2.xls");
			System.out.println(f.getCanonicalPath());
			if(!f.exists()&& !f2.exists()) return null;
			

		} catch (FileNotFoundException e1) {
			log.error(e1);
			return null;
		} catch (IOException e) {
			log.error(e);
			return null;
		}

		try{
			if(f.exists()){
				inputStream = new FileInputStream(f);
				this.body(inputStream, list,excelFilePath);
			}
			if(f2.exists()){
				inputStream2 = new FileInputStream(f2);
				body(inputStream2,  list,excelFilePath2);
			}
			System.out.println(list.size());
		}catch(Exception e){
			e.printStackTrace();
		}

		return list;
	}

	private void body(FileInputStream inputStream ,List<CarPartsInfo> list,String excelFilePath) {
		jxl.Workbook rwb = null;
		WorkbookSettings workbookSettings = new WorkbookSettings();
		workbookSettings.setEncoding("GBK"); //解决中文乱码，或GBK
		try {
			rwb = jxl.Workbook.getWorkbook(inputStream, workbookSettings);
			jxl.Sheet firstSheet = rwb.getSheet(0);   
			for (int j = 0; j < firstSheet.getRows(); j++) {  
				Cell[]  cells = firstSheet.getRow(j);
				CarPartsInfo c = new CarPartsInfo(); 
				if(cells[0] != null && !cells[0].getContents().equals("partno")) {
					c.setPartsNo(cells[PARTS_NO].getContents());
					if(cells[PARTS_NO] != null)
						c.setStandardPartsNo(cells[PARTS_NO].getContents());

					if(cells[PARTS_NAME] != null)
						c.setCname(cells[PARTS_NAME].getContents());
					c.setEname(null);

					if(cells[PARTS_CAR] != null) 
						c.setCarInfo(cells[PARTS_CAR].getContents());

					c.setIsOem("0");


					if(cells[PARTS_MANUF_NAME] != null) {
						c.setManufactureCname(cells[PARTS_MANUF_NAME].getContents());
						c.setManufactureEname(null);
					}
					if(cells[PARTS_PRODUCING]!= null) { 
						c.setProducingArea(cells[PARTS_PRODUCING].getContents());
					}

					if(cells[PARTS_UNIT] != null)
						c.setUnit(cells[PARTS_UNIT].getContents());
					/*
		    				if(cells[PARTS_STOCK] != null)
		    					c.setStock(new Long(cells[PARTS_STOCK].getContents()));
		    				if(cells[PARTS_BATCH_PRICE] != null) 
		                			c.setBatchPrice(new Double(cells[PARTS_BATCH_PRICE].getContents()));
		    				if(cells[PARTS_PRICE] != null) 
		    					c.setPrice(new Double(cells[PARTS_PRICE].getContents())); */
					c.setCostPrice(0);
					c.setOwnNo(cells[PARTS_WAREHOUSE].getContents());

					if(  cells[PARTS_STOCK] != null &&    StringUtils.isNotEmpty( cells[PARTS_STOCK].getContents())    ){//库存
						Double dddd = Double.valueOf( cells[PARTS_STOCK].getContents());
						DecimalFormat dfd = new DecimalFormat("#");
						c.setStock( Long.valueOf(dfd .format(dddd)  ) );
					}
					if(   cells[PARTS_BATCH_PRICE] != null &&   StringUtils.isNotEmpty( cells[PARTS_BATCH_PRICE].getContents()) ){ 
						Double dd2= Double.valueOf(cells[PARTS_BATCH_PRICE].getContents());
						DecimalFormat df = new DecimalFormat("#.##");
						c.setBatchPrice(new Double(df.format(dd2)));
					}
					if(    cells[PARTS_STOCK] != null &&   StringUtils.isNotEmpty(  cells[PARTS_STOCK].getContents())   ) {
						Double dd2= Double.valueOf(cells[PARTS_STOCK].getContents() );
						DecimalFormat df = new DecimalFormat("#.##");
						c.setPrice(new Double(df.format(dd2))); 
					}
					//		    				System.out.println(c.toString());

					//c = brandAnalyze(c);
					list.add(c);
				}
			} 
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally{
			rwb.close();
			try {
				inputStream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			archiveXls(excelFilePath);

		}

	}


	private CarPartsInfo brandAnalyze(CarPartsInfo c) {
		if(c.getStandardPartsNo()!=null) {
			String lastChar = c.getStandardPartsNo().substring(c.getStandardPartsNo().length()-1, c.getStandardPartsNo().length());
			if(lastChar != null && !StringUtils.isNumeric(lastChar)) {
				String brand = StringUtils.substringAfterLast(c.getStandardPartsNo(), " ");
				String stardNo = StringUtils.substringBeforeLast(c.getStandardPartsNo(), " ");
				//				System.out.println("brand:" + brand);
				//				System.out.println("standardPartNo:" + stardNo);
				c.setManufactureCname(brand);
				c.setStandardPartsNo(stardNo);
			}
		}
		return c;
	}



	public boolean archiveXls(String excelFilePath) {
		File file = new File(excelFilePath);
		if(file.exists() && file.delete()){
			log.info(file.getName() + " is deleted!");
		}else{
			log.info("Delete operation is failed for file:" + excelFilePath);
		}

		return false;
	}
	public static void main(String  [] args) {
		JRCarPartsInfoReader reader = new JRCarPartsInfoReader();
		reader.getCarPartsListByJxl();
	}

}
